import pandas as pd

from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

import util.lib as lib
import util.mydecorator as mydecorator

@mydecorator.calTime
def job():
    # 加载 Excel 文件
    wb = load_workbook(lib.data_path + 'lp.xlsx')

    # 颜色
    red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
    blue_fill = PatternFill(start_color='66B3FF', end_color='66B3FF', fill_type='solid')
    green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
    yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    duration_rule = CellIsRule(operator='lessThanOrEqual', formula=[32], stopIfTrue=True, fill=green_fill)
    inc_num_rule = CellIsRule(operator='lessThanOrEqual', formula=[-3], stopIfTrue=True, fill=yellow_fill)

    etf_rule = CellIsRule(operator='equal', formula=['"ETF"'], stopIfTrue=True, fill=blue_fill)

    eq0_rule = CellIsRule(operator='equal', formula=[0], stopIfTrue=True, fill=red_fill)

    for sheet_name in wb.sheetnames:
        # 获取当前 Sheet
        sheet = wb[sheet_name]

        # 冻结首行（第一行）
        sheet.freeze_panes = 'A2'  # 表示冻结 A2 单元格上方的行（即第一行）

        # 设置第一列的宽
        sheet.column_dimensions['B'].width = 20 


        # inc_num
        sheet.conditional_formatting.add('F2:F550', inc_num_rule)

        # 上市月份
        sheet.conditional_formatting.add('U2:U550', duration_rule)

        sheet.conditional_formatting.add('V2:V550', etf_rule)

        sheet.conditional_formatting.add('Q2:Q550', eq0_rule)

    

    wb.save(lib.data_path + 'lp_v.xlsx')


if __name__ == '__main__':
    job()